﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entidades;

namespace AccesoBD
{
    public class Instituciones
    {
        public static bool registrarInstitucion(Institucion inst, ConvenioInstitucion convenio)
        {
            bool res = false;
            SqlConnection conexion = DB.Conectar();
            SqlTransaction trans = null;
            try
            {
                trans = conexion.BeginTransaction();

                //insertamos el domicilio
                string comando = "INSERT INTO Domicilio (calle, numero, piso, departamento, idBarrio, codigoPostal) "
                               + "VALUES (@calle,@nro,@piso,@depto,@idBarrio,@codPostal)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@calle", inst.Domicilio.calle));
                sqlcmd.Parameters.Add(new SqlParameter("@nro", inst.Domicilio.numero));
                if(inst.Domicilio.piso == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@piso", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@piso", inst.Domicilio.piso));
                if(inst.Domicilio.Departamento == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@depto", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@depto", inst.Domicilio.Departamento));
                sqlcmd.Parameters.Add(new SqlParameter("@idBarrio", inst.Domicilio.idBarrio));
                if(inst.Domicilio.codPostal == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@codPostal", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@codPostal", inst.Domicilio.codPostal));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();

                //obtenemos el id del domicilio
                comando = "SELECT @@identity FROM Domicilio";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Transaction = trans;
                int idDom = int.Parse(sqlcmd.ExecuteScalar().ToString());
                inst.IdDomicilio = idDom;

                //insertamos la institucion
                comando = "INSERT INTO Institucion (nombreInstitucion, nombreResponsable, apellidoResponsable, idDomicilio, telefono) "
                        + "VALUES (@nombreInst,@nombreResp,@apellidoResp,@idDomicilio,@telefono)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@nombreInst", inst.NombreInstitucion));
                sqlcmd.Parameters.Add(new SqlParameter("@nombreResp", inst.NombreResponsable));
                sqlcmd.Parameters.Add(new SqlParameter("@apellidoResp", inst.ApellidoResponsable));
                sqlcmd.Parameters.Add(new SqlParameter("@idDomicilio", inst.IdDomicilio));
                sqlcmd.Parameters.Add(new SqlParameter("@telefono", inst.Telefono));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();

                //obtenemos el id de la institucion
                comando = "SELECT @@identity FROM Institucion";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Transaction = trans;
                int idInst = int.Parse(sqlcmd.ExecuteScalar().ToString());
                inst.IdInstitucion = idInst;

                //insertamos el convenio
                comando = "INSERT INTO Convenio_Institucion (idInstitucion, fechaVigenciaDesde, fechaVigenciaHasta, montoMensual, observaciones, cantPersonasCubiertas) "
                        + "VALUES (@idInst,@fechaDesde,@fechaHasta,@monto,@observaciones,@cantPersonas)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@idInst", inst.IdInstitucion));
                sqlcmd.Parameters.Add(new SqlParameter("@fechaDesde", convenio.FechaVigenciaDesde));
                if(convenio.FechaVigenciaHasta == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", convenio.FechaVigenciaHasta));
                sqlcmd.Parameters.Add(new SqlParameter("@monto", convenio.MontoMensual));
                if (string.IsNullOrEmpty(convenio.Observaciones))
                    sqlcmd.Parameters.Add(new SqlParameter("@observaciones", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@observaciones", convenio.Observaciones));
                sqlcmd.Parameters.Add(new SqlParameter("@cantPersonas", convenio.CantPersonasCubiertas));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();

                //insertamos la cuota_afiliado


                comando = "INSERT INTO Cuota_Afiliado(mes, año, idInstitucion, fechaVencimiento, importe) VALUES (@mes,@año,@nroAfiliado,@fechaVencimiento, @importe)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@mes", DateTime.Now.Month));
                sqlcmd.Parameters.Add(new SqlParameter("@año", DateTime.Now.Year));
                sqlcmd.Parameters.Add(new SqlParameter("@nroAfiliado", idInst));
                DateTime fechaVencimiento = DateTime.Now.AddMonths(1);
                sqlcmd.Parameters.Add(new SqlParameter("@fechaVencimiento", fechaVencimiento));
                sqlcmd.Parameters.Add(new SqlParameter("@importe", convenio.MontoMensual));
                sqlcmd.Transaction = trans;
               int filas= sqlcmd.ExecuteNonQuery();

                if (filas > 0)
                {
                    res = true;
                    trans.Commit();
                }
                
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                conexion.Close();
            }
            return res;
        }

        public static List<Institucion> obtenerInstituciones()
        {
            List<Institucion> list = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT idInstitucion, nombreInstitucion, nombreResponsable, apellidoResponsable, telefono FROM Institucion";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                SqlDataReader dr = sqlcmd.ExecuteReader();

                Institucion inst = null;
                list = new List<Institucion>();
                while (dr.Read())
                {
                    inst = new Institucion();
                    inst.IdInstitucion = int.Parse(dr["idInstitucion"].ToString());
                    inst.NombreInstitucion = dr["nombreInstitucion"].ToString();
                    inst.NombreResponsable = dr["nombreResponsable"].ToString();
                    inst.ApellidoResponsable = dr["apellidoResponsable"].ToString();
                    inst.Telefono = long.Parse(dr["telefono"].ToString());

                    list.Add(inst);
                    inst = null;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }

            return list;
        }

        public int obtenerIdInstitucion ( string nombre)
        {
            int id = 0;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT idInstitucion FROM Institucion WHERE (nombreInstitucion= @nombre)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@nombre", nombre));
                SqlDataReader dr = sqlcmd.ExecuteReader();

                
               
                while (dr.Read())
                {
                    if (dr["idInstitucion"].ToString() != null)
                        id = Convert.ToInt32(dr["idInstitucion"].ToString());
                    else id = 0;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }

            return id;
        }

        public static List<Institucion> obtenerInstituciones(string nombre)
        {
            List<Institucion> list = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT idInstitucion, nombreInstitucion, apellidoResponsable, nombreResponsable, telefono FROM Institucion WHERE (nombreInstitucion LIKE '%@nombre%')";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@nombre", nombre));
                SqlDataReader dr = sqlcmd.ExecuteReader();

                Institucion inst = null;
                list = new List<Institucion>();
                while (dr.Read())
                {
                    inst = new Institucion();
                    inst.IdInstitucion = int.Parse(dr["idInstitucion"].ToString());
                    inst.NombreInstitucion = dr["nombreInstitucion"].ToString();
                    inst.NombreResponsable = dr["nombreResponsable"].ToString();
                    inst.ApellidoResponsable = dr["apellidoResponsable"].ToString();
                    inst.Telefono = long.Parse(dr["telefono"].ToString());

                    list.Add(inst);
                    inst = null;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }

            return list;
        }

        public static Institucion obtenerInstitucion(int id)
        {
            Institucion inst = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT Institucion.nombreInstitucion, Institucion.nombreResponsable, Institucion.idInstitucion, Institucion.apellidoResponsable, Institucion.idDomicilio, Institucion.telefono, Domicilio.calle, "
                               + "Domicilio.numero, Domicilio.piso, Domicilio.departamento, Domicilio.idBarrio, Domicilio.codigoPostal "
                               + "FROM Institucion INNER JOIN Domicilio ON Institucion.idDomicilio = Domicilio.idDomicilio WHERE (Institucion.idInstitucion = @idInst)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@idInst", id));
                SqlDataReader dr = sqlcmd.ExecuteReader();

                if (dr.HasRows)
                {
                    inst = new Institucion();
                    while (dr.Read())
                    {
                        //tomamos los datos de la institucion
                        inst.IdInstitucion = int.Parse(dr["idInstitucion"].ToString());
                        inst.NombreInstitucion = dr["nombreInstitucion"].ToString();
                        inst.NombreResponsable = dr["nombreResponsable"].ToString();
                        inst.ApellidoResponsable = dr["apellidoResponsable"].ToString();
                        inst.Telefono = long.Parse(dr["telefono"].ToString());

                        //cargamos el domicilio
                        inst.IdDomicilio = int.Parse(dr["idDomicilio"].ToString());
                        inst.Domicilio = new Domicilio();
                        inst.Domicilio.calle = dr["calle"].ToString();
                        inst.Domicilio.numero = int.Parse(dr["numero"].ToString());
                        if (dr["piso"] == DBNull.Value)
                            inst.Domicilio.piso = null;
                        else
                            inst.Domicilio.piso = int.Parse(dr["piso"].ToString());
                        if (dr["departamento"] == DBNull.Value)
                            inst.Domicilio.Departamento = null;
                        else
                            inst.Domicilio.Departamento = dr["departamento"].ToString();
                        if (dr["codigoPostal"] == DBNull.Value)
                            inst.Domicilio.codPostal = null;
                        else
                            inst.Domicilio.codPostal = int.Parse(dr["codigoPostal"].ToString());
                        inst.Domicilio.idBarrio = int.Parse(dr["idBarrio"].ToString());
                        
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return inst;
        }

        //obtenemos SOLO el ultimo convenio, el que se encuentra vigente.
        public static ConvenioInstitucion obtenerConvenioDeInstitucion(int idInst)
        {
            ConvenioInstitucion convenio = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT fechaVigenciaDesde, idConvenioInstitucion, fechaVigenciaHasta, montoMensual, observaciones, cantPersonasCubiertas, idInstitucion "
                               + "FROM Convenio_Institucion WHERE (idInstitucion = @idInst) AND (fechaVigenciaHasta IS NULL OR fechaVigenciaHasta > GETDATE())";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@idInst", idInst));
                SqlDataReader dr = sqlcmd.ExecuteReader();

                if (dr.HasRows)
                {
                    convenio = new ConvenioInstitucion();
                    while (dr.Read())
                    {
                        convenio.IdConvenioInstitucion = int.Parse(dr["idConvenioInstitucion"].ToString());
                        convenio.IdInstitucion = int.Parse(dr["idInstitucion"].ToString());
                        convenio.FechaVigenciaDesde = DateTime.Parse(dr["fechaVigenciaDesde"].ToString());
                        if (dr["fechaVigenciaHasta"] == DBNull.Value)
                            convenio.FechaVigenciaHasta = null;
                        else
                            convenio.FechaVigenciaHasta = DateTime.Parse(dr["fechaVigenciaHasta"].ToString());
                        convenio.MontoMensual = float.Parse(dr["montoMensual"].ToString());
                        convenio.CantPersonasCubiertas = int.Parse(dr["cantPersonasCubiertas"].ToString());
                        if (dr["observaciones"] == DBNull.Value)
                            convenio.Observaciones = null;
                        else
                            convenio.Observaciones = dr["observaciones"].ToString();
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return convenio;
        }

        public static bool modificarInstitucion(Institucion inst)
        {
            bool res = false;
            SqlConnection conexion = DB.Conectar();
            SqlTransaction trans = null;
            try
            {
                trans = conexion.BeginTransaction();

                //modificamos el domicilio
                string comando = "UPDATE Domicilio SET calle = @calle, numero = @nro, piso = @piso, departamento = @depto, idBarrio = @idBarrio, codigoPostal = @codPostal "
                               + "WHERE (idDomicilio = @idDomicilio)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@calle", inst.Domicilio.calle));
                sqlcmd.Parameters.Add(new SqlParameter("@nro", inst.Domicilio.numero));
                if(inst.Domicilio.piso == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@piso", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@piso", inst.Domicilio.piso));
                if(inst.Domicilio.Departamento == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@depto", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@depto", inst.Domicilio.Departamento));
                sqlcmd.Parameters.Add(new SqlParameter("@idBarrio", inst.Domicilio.idBarrio));
                if(inst.Domicilio.codPostal == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@codPostal", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@codPostal", inst.Domicilio.codPostal));
                sqlcmd.Parameters.Add(new SqlParameter("@idDomicilio", inst.IdDomicilio));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();


                //modificamos la institucion
                comando = "UPDATE Institucion SET nombreInstitucion = @nombreInst, nombreResponsable = @nombreResp, apellidoResponsable = @apellidoResp, telefono = @telefono "
                        + "WHERE (idInstitucion = @idInstitucion)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@nombreInst", inst.NombreInstitucion));
                sqlcmd.Parameters.Add(new SqlParameter("@nombreResp", inst.NombreResponsable));
                sqlcmd.Parameters.Add(new SqlParameter("@apellidoResp", inst.ApellidoResponsable));
                sqlcmd.Parameters.Add(new SqlParameter("@telefono", inst.Telefono));
                sqlcmd.Parameters.Add(new SqlParameter("@idInstitucion", inst.IdInstitucion));
                sqlcmd.Transaction = trans;
                int filas = sqlcmd.ExecuteNonQuery();

                
                if (filas > 0)
                {
                    res = true;
                    trans.Commit();
                }
                
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                conexion.Close();
            }
            return res;
        }

        public static bool registrarNuevoConvenioInstitucion(ConvenioInstitucion convenio, DateTime fechaBajaConvenioActual)
        {
            bool res = false;
            SqlConnection conexion = DB.Conectar();
            SqlTransaction trans = null;
            try
            {
                trans = conexion.BeginTransaction();

                string comando = "UPDATE Convenio_Institucion SET fechaVigenciaHasta = @fechaBaja WHERE (idConvenioInstitucion = @idConvenio)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@fechaBaja", fechaBajaConvenioActual));
                sqlcmd.Parameters.Add(new SqlParameter("@idConvenio", convenio.IdConvenioInstitucion));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();

                comando = "INSERT INTO Convenio_Institucion (idInstitucion, fechaVigenciaDesde, fechaVigenciaHasta, montoMensual, observaciones, cantPersonasCubiertas) "
                        + "VALUES (@idInst,@fechaDesde,@fechaHasta,@monto,@observaciones,@cantPersonas)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@idInst", convenio.IdInstitucion));
                sqlcmd.Parameters.Add(new SqlParameter("@fechaDesde", convenio.FechaVigenciaDesde));
                if (convenio.FechaVigenciaHasta == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", convenio.FechaVigenciaHasta));
                sqlcmd.Parameters.Add(new SqlParameter("@monto", convenio.MontoMensual));
                sqlcmd.Parameters.Add(new SqlParameter("@cantPersonas", convenio.CantPersonasCubiertas));
                if(string.IsNullOrEmpty(convenio.Observaciones))
                    sqlcmd.Parameters.Add(new SqlParameter("@observaciones", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@observaciones", convenio.Observaciones));
                sqlcmd.Transaction = trans;
                int filas =  sqlcmd.ExecuteNonQuery();

                if (filas > 0)
                {
                    trans.Commit();
                    res = true;
                }
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                conexion.Close();
            }
            return res;
        }

        
    }
}
